﻿/* 
 * Since 2008 - 2012
 */

package com.hh.bss.customer.dao.impl;

import java.util.*;

import com.hh.bss.auth.entity.SystemUser;
import com.hh.bss.auth.util.AuthSqlUtil;
import com.hh.bss.base.*;
import com.hh.bss.common.ConstantDic;
import com.hh.bss.common.MapObjectRowMapper;
import com.hh.bss.common.cache.SystemUserCache;
import com.hh.bss.common.cache.TimeCacheManager;
import com.hh.bss.common.page.*;
import com.hh.bss.common.page.impl.*;
import com.hh.bss.customer.dao.*;
import com.hh.bss.customer.entity.*;
import com.hh.bss.customer.service.*;
import com.hh.bss.customer.vo.query.*;
import com.hh.bss.util.*;





import static com.hh.bss.util.ObjectUtils.*;

import org.springframework.stereotype.Repository;

@Repository
public class CustomerContractDaoImpl extends BaseHibernateDao<CustomerContract,java.lang.Integer>  implements CustomerContractDao<CustomerContract,java.lang.Integer>{

	public Class getEntityClass() {
		return CustomerContract.class;
	}
	
	public Page findPage(CustomerContractQuery query) {
        //XsqlBuilder syntax,please see http://code.google.com/p/rapid-xsqlbuilder
        // [column]为字符串拼接, {column}为使用占位符. [column]为使用字符串拼接,如username='[username]',偷懒时可以使用字符串拼接 
        // [column] 为PageRequest的属性
        
		
		    Class[] os={CustomerContractQuery.class};
    		Class property= org.springframework.beans.BeanUtils.findPropertyType(query.getField_type(), os);
        if (property!=null){
        	 if ("java.lang.String".equals(property.getName())){
	    			
	    			com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(),query.getQuery());
	    			
	    		}
	    		else if ("java.lang.Integer".equals(property.getName())){
	    			
	    			com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseInteger(query.getQuery(), 0));
	    			
	    		}
	    		else if ("java.lang.Double".equals(property.getName())){
	    			com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseDouble(query.getQuery(), 0));
	    		}
	    		else if ("java.util.Date".equals(property.getName())){
	    			com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), DateHelper.toDate(query.getQuery(), DateHelper.DEFAULT_DATE_FORMAT));
	    		}
        }	 
        //生成sql2的原因是为了不喜欢使用xsqlbuilder的同学，请修改生成器模板，删除本段的生成
        StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		sql.append(" select * from ( ");
		sql.append(" select a.id,a.title,a.nbr,a.cust_id custId,c.name custName,c.abbr_name abbrName,c.full_name custNameEn,a.contract_style contractStyle,a.contract_type contractType,a.lang,a.ar_rate arRate,a.min_ar minAr,a.begin_d beginD,a.end_d endD,a.ar_d arD,a.pre_ar preAr,a.pre_ar_d preArD,a.pre_unit preUnit,a.trial_months trialMonths,a.refund_rate refundRate,a.delay_rate delayRate,a.search_limit searchLimit,a.comment,a.creator,a.create_dt createDt,a.modifier,a.modified_dt modifiedDt   ");
		sql.append(" ,d.account manager,e.deptNames  ");
		sql.append(" from customer_contract a ");
		sql.append(" inner join customer b on a.cust_id=b.id ");
		sql.append(" inner join company  c on b.company_id=c.id ");
		sql.append(" left  join (select a.account,a.contract_id from customer_contract_staff a where a.role = 'BDM' group by a.contract_id,a.role,a.account ) d on d.contract_id = a.id  ");
		//部门
		sql.append(" left join (select a.account,group_concat(b.`name`) deptNames from system_user a ,system_branch_dept b where  find_in_set(b.id,a.dept_id) group by a.account ) e on e.account = d.account  ");
		sql.append(" ) t where 1=1  ");
		if(isNotEmpty(query.getId())) {
            sql.append(" and  t.id = ? ");
            values.add(query.getId());
            
        }
		if(isNotEmpty(query.getCustId())) {
            //sql.append(" and  t.custId = ? ");
            //values.add(query.getCustId());
            
            //增加合同服务公司共享联系人
			sql.append(" and ( t.custId=? ");
			sql.append(" or exists(select 1 from customer_contract a, customer_contract_service_company b where a.cust_id=t.custId and a.id=b.contract_id  and b.cust_id=?) ");
			sql.append(" )");
			values.add(query.getCustId());
			values.add(query.getCustId());
            
        }
		if(isNotEmpty(query.getCustName())) {
            sql.append(" and ( t.custName like ?  or t.custNameEn like ? or t.abbrName  like  ? )");
            values.add("%"+query.getCustName()+"%");
            values.add("%"+query.getCustName()+"%"); 
            values.add("%"+query.getCustName()+"%");

        }
		if(isNotEmpty(query.getNbr())) {
            sql.append(" and  t.nbr like ? ");
            values.add("%"+query.getNbr()+"%");
        }
		
		if(isNotEmpty(query.getTitle())) {
            sql.append(" and  t.title like ? ");
            values.add("%"+query.getTitle()+"%");
        }
		if(isNotEmpty(query.getBeginD())) {
            sql.append(" and  t.beginD = ? ");
            values.add(query.getBeginD());
        }
		if(isNotEmpty(query.getEndD())) {
            sql.append(" and  t.endD = ? ");
            values.add(query.getEndD());
        }
		if(isNotEmpty(query.getContractStyle())) {
            sql.append(" and  t.contractStyle = ? ");
            values.add(query.getContractStyle());
        }
		if(isNotEmpty(query.getContractType())) {
            sql.append(" and  t.contractType = ? ");
            values.add(query.getContractType());
        }
        //增加权限控制		
		
		if (query.getOnlineUser()!=null){
			String authsql=authSQL(query.getOnlineUser().getUsername(), "t.creator");
			if (!StringHelper.isEmpty(authsql)) sql.append(authsql);
			
		}	
        if(isNotEmpty(query.getSortColumns())){
        	sql.append(" order by t.").append(query.getSortColumns());
        }else{
        	sql.append("order by  ifnull(t.modifiedDt,t.createDt) desc");
        	//if(query.ge't'c)
        	//sql.append(" order by t.modifiedDt desc ");
        }
        
        log.info("合同查询SQL:"+sql.toString());
        
		return super.getJdbcDao().createPage(sql.toString(),values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());
	}
	private   String authSQL(String username,String creatorColumn){
		
		/*
		 * 	GM-可以看到所有人的合同，所有合同的附件详情。
			BM-可以看到所有人的合同，但只能看到自己区域的人的合同附件详情。
			M-可以看到自己组内的所有合同，包括组内合同的所有附件详情。 
			//update 2013年4月3日10:24:45 M-可以看到所有人的合同，但只能看到组内合同的所有附件详情。
			C-可以看到自己组内的所有合同，但只能看到属于自己参与的合同的附件详情。
			Account-可以看到所有人的合同，所有合同的附件详情。
			Contractor-可以看到所有人的合同，所有合同的附件详情。
		 */
		
		//admin不受控制
		if (ConstantDic.ADMIN_USER.equals(username)) return "";
		
		SystemUser systemUser=SystemUserCache.getSystemUserByUsername(username);
		
		StringBuffer sb=new StringBuffer();
		
		//Account 角色 看全部
        //GM 看全部
		//BM 全部
		if (SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_ACCOUNT) || SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_GM) || SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_Branch_Manager) || SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_SUPER_USER))
			return "";
		
		//Contractor看全部
		if (SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_CONTRACTOR)) return "";
		//M 全部
		if(SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_Manager)) return "";
		// C R 可以看到自己组内的所有合同
		sb.append(" and (");
		sb.append(creatorColumn).append("='").append(username).append("'");
		if( SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_Consultant) ){
//			sb.append(" or  exists ( select 1 from customer_contract_staff t3 where  t.id=t3.contract_id  and   t3.account in ");
//			sb.append(" (select b.account from system_user b where b.dept_id = ");
//			sb.append(" (select ss1.dept_id from system_user ss1 where ss1.account='");
//			sb.append(username).append("')))");
			
			sb.append(" or  exists ( select 1 from customer_contract_staff t3 where  t.id=t3.contract_id  and   t3.account in ");
			sb.append(" ( ").append(AuthSqlUtil.getSameDeptStaffSql(username)).append(" )) ");
			
			//sb.append(" or exists ( select 1 from customer_contract_staff t3 where  t.id=t3.contract_id and t3.account in (").append(" select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='").append(username).append("'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='").append(username).append("')) ").append(" ) ) ");
		}
        //合同--》客户--》职位--》职位下相关的人员 可以看到 客户相关的合同		
		//
		/*
		sb.append(" or exists ( select 1 from customer_contract_staff t3 where  t.id=t3.contract_id and t3.account= '").append(username).append("' ) ");
		sb.append(" or exists ( select 1 from contract_job t1,contract_job_partner t2 where  t1.cust_id=t.custId and t1.id=t2.job_id and t2.account= '").append(username).append("' ) ");
		//自己下级创建的
		sb.append(" or ").append(creatorColumn).append(" in ( select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='").append(username).append("'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='").append(username).append("')) )");
		//下级看那得到的
		//下级是合同合作人员
		sb.append(" or exists ( select 1 from customer_contract_staff t3 where  t.id=t3.contract_id and t3.account in (").append(" select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='").append(username).append("'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='").append(username).append("')) ").append(" ) ) ");
		//select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.id in(select b2.title_id from system_user b2 where b2.account='").append(username).append("'))
		//下级是职位合作人员
		sb.append(" or exists ( select 1 from contract_job t1,contract_job_partner t2 where  t1.cust_id=t.custId and t1.id=t2.job_id and t2.account in (").append(" select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='").append(username).append("'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='").append(username).append("')) ").append(" ) ) ");
//		sb.append(" or exists ( select 1 from contract_job t1,contract_job_partner t2 where  t2.cust_id=t.custId and t2.role in('"+ConstantDic.JOB_PARTNER_TYPE_MC+"','"+ConstantDic.JOB_PARTNER_TYPE_PC+"') and t1.id=t2.job_id and t2.account in (").append(" select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='").append(username).append("'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='").append(username).append("')) ").append(" ) ) ");
		*/
		sb.append(" )");
		//if (systemUser.getId()) return "";
		
		//
		
		
		
		
		return sb.toString();
	}
	

}
